package cn.itlaobing.dao;

import cn.itlaobing.entity.VegetablesDTO;
import cn.itlaobing.entity.VegetablesImport;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description:
 * @Author: WangDong
 * @Date: 2017/11/10 16:09
 */
public class VegetableDao extends BaseDao{

    public List<VegetablesDTO> findAll(int pageIndex,int pageSize) throws SQLException {
        QueryRunner queryRunner=new QueryRunner(getDataSource());
        String sql="SELECT v2.id,v1.name,v2.quantity FROM vegetablestypes v1,vegetablesimportrecord v2 WHERE v1.id=v2.typeId limit"+((pageIndex-1)*pageSize)+","+pageSize;
        BeanListHandler<VegetablesDTO> beanListHandler=new BeanListHandler<VegetablesDTO>(VegetablesDTO.class);
        List<VegetablesDTO> list=queryRunner.query(sql, new ResultSetHandler<List<VegetablesDTO>>() {
            @Override
            public List<VegetablesDTO> handle(ResultSet rs) throws SQLException {
                List<VegetablesDTO> list=new ArrayList<VegetablesDTO>();
                while(rs.next()){
                   VegetablesDTO vegetablesDTO=new VegetablesDTO();
                    vegetablesDTO.setId(rs.getString(1));
                    vegetablesDTO.setName(rs.getString(2));
                    vegetablesDTO.setQuantity(rs.getFloat(3));
                    list.add(vegetablesDTO);


                }
                return list;
            }
        });
        return list;
    }





    public List<VegetablesDTO> queryAll() throws SQLException {
        QueryRunner queryRunner=new QueryRunner(getDataSource());
        String sql="SELECT v2.id,v1.name,v2.quantity FROM vegetablestypes v1,vegetablesimportrecord v2 WHERE v1.id=v2.typeId";
        BeanListHandler<VegetablesDTO> beanListHandler=new BeanListHandler<VegetablesDTO>(VegetablesDTO.class);
        List<VegetablesDTO> list=queryRunner.query(sql,beanListHandler);
        return list;
    }

    public int update(VegetablesDTO vegetablesDTO) throws SQLException {
        QueryRunner queryRunner=new QueryRunner(getDataSource());
        String sql="SELECT * FROM (SELECT v2.id,v1.name,v2.quantity FROM vegetablestypes v1,vegetablesimportrecord v2 WHERE v1.id=v2.typeId )t WHERE t.id=?";
        BeanHandler<VegetablesDTO>beanHandler=new BeanHandler<VegetablesDTO>(VegetablesDTO.class);
        VegetablesDTO vegetablesDTO1= queryRunner.query(sql,beanHandler,vegetablesDTO.getId());
        if(vegetablesDTO1==null){
            return -1;
        }
        sql="update vegetablesimportrecord set quantity=? where id=?";
        int r1=queryRunner.update(sql,vegetablesDTO.getQuantity(),vegetablesDTO.getId());
        sql="UPDATE vegetablestypes SET NAME=?  WHERE id=( SELECT typeId FROM vegetablesimportrecord WHERE id=?)";
        int r2=queryRunner.update(sql,vegetablesDTO.getName(),vegetablesDTO.getId());
        if(r1>0 && r2>0){
            return r1;
        }
        return -1;
    }
    public int deleteById(String id) throws SQLException {
        QueryRunner queryRunner=new QueryRunner(getDataSource());
        String sql="select * from vegetablesimportrecord where id=?";
        BeanHandler<VegetablesImport>beanHandler=new BeanHandler<VegetablesImport>(VegetablesImport.class);
        VegetablesImport vegetablesImport= queryRunner.query(sql,beanHandler,id);
        if(vegetablesImport==null){
            return -1;

        }
        sql="delete from vegetablesimportrecord where id=?";
        int r1=queryRunner.update(sql,id);
        sql="DELETE FROM vegetablestypes WHERE id=(SELECT typeId FROM vegetablesimportrecord WHERE id=?)";
        int r2=queryRunner.execute(sql,id);
        if(r1>0 && r2>0){
            return r1;
        }
        return -1;



    }
}
